package cn.edu.glut.choosethesis.repository;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import cn.edu.glut.vo.SubjectToTeacherVO;
/**
 * 
 * @author Caizhfy
 * @version v.0.1
 * @createTime 2017年12月18日下午3:48:12
 * @email Caizhfy@163.com
 * @description 
 *
 */
@Repository
public class SubjectRepositoryImpl {
	
	@PersistenceContext
	private EntityManager em;
	
	/*
	 * 原生主sql：
	 * select su.id, su.title, count(se.id), max(se.is_select), if(max(se.is_select>0),s.nickname,null) from `subject` su 
	 *	LEFT JOIN selection se ON se.subject_id=su.id 
	 *	LEFT JOIN student s ON se.student_id=s.id
	 *	GROUP BY su.id
	 */
	/**
	 * 动态查询
	 * @param pageable
	 * @param isSelect	是否确选（只要非空，都相当于加了条件）
	 * @param titleLike	根据title模糊查询
	 * @param teacherId 根据老师的id查询
	 * @return
	 */
	Page<SubjectToTeacherVO> findAllSubjectToTeacherVO(Pageable pageable, Boolean isSelect, String titleLike,
			String teacherId, String majorId, String studentId){
		//条件组合
		StringBuffer where = new StringBuffer(" where 1=1 ");
		StringBuffer having = new StringBuffer();
		if(isSelect != null){
			if(isSelect)
				having.append(" having max(se.isSelection)>0 ");
			else
				having.append(" having ((max(se.isSelection) is null) or max(se.isSelection)<=0) ");
		}
		if(!StringUtils.isEmpty(titleLike))
			where.append(" and su.title like :titleLike");
		if(!StringUtils.isEmpty(teacherId))
			where.append(" and su.teacher.id=:teacherId");
		if(!StringUtils.isEmpty(majorId))
			where.append(" and su.major.id=:majorId");
		if(!StringUtils.isEmpty(studentId)){
			where.append(" and su.major.id=(select stu.major.id from Student stu where stu.id=:studentId)");
		}
		//主jpql 由于不能使用 if(exp1,rsul1,rsul2)只能用case when exp1 then rsul1 else rsul2 end
		String jpql = "select new cn.edu.glut.vo.SubjectToTeacherVO(su.id, su.title, cast(count(se.id) as int) as guysNum, max(se.isSelection) as choose, "
				+ " (select ss.nickname from Selection as sel left join sel.student as ss where sel.subject.id=su.id and sel.isSelection=1) as stuName, "
				+ " (select t.nickname from Teacher t where t.id=su.teacher.id) as teacherName, "
				+ " ma.id as majorId, ma.name as majorName) "
				+ " from Subject as su left join su.selections as se"
				+ " left join su.major as ma "
				+ where
				+ " group by su.id "
				+ having;
		
		String countJpql = null;
		if(isSelect != null)
			countJpql = "select count(*) from Subject su left join su.selections as se left join se.student as s"
					+ where
					+ " and su.id in(select s.id from Subject s left join s.selections se group by s.id "
					+  having
					+ " )";
		else
			countJpql = "select count(*) from Subject su left join su.selections as se left join se.student as s" + where;
		Query query = em.createQuery(jpql, SubjectToTeacherVO.class);
		Query countQuery = em.createQuery(countJpql);
//		pageable.getPageNumber()==0 ? pageable.getOffset() :  pageable.getOffset()-5
		if(null != pageable){
			query.setFirstResult(pageable.getOffset());
			query.setMaxResults(pageable.getPageSize());
		}
		if(!StringUtils.isEmpty(titleLike)){
			query.setParameter("titleLike", "%"+titleLike+"%");
			countQuery.setParameter("titleLike", "%"+titleLike+"%");
		}
		if(!StringUtils.isEmpty(teacherId)){
			query.setParameter("teacherId", teacherId);
			countQuery.setParameter("teacherId", teacherId);
		}
		if(!StringUtils.isEmpty(majorId)){
			query.setParameter("majorId", majorId);
			countQuery.setParameter("majorId", majorId);
		}
		if(!StringUtils.isEmpty(studentId)){
			query.setParameter("studentId", studentId);
			countQuery.setParameter("studentId", studentId);
		}
		List<SubjectToTeacherVO> voList = query.getResultList();
		return new PageImpl<SubjectToTeacherVO>(voList, pageable, Integer.valueOf(countQuery.getSingleResult().toString()));
	}
}
